<?php
class model_formula extends framework_base_model {
	/**
	 * @desc 添加公式
	 * @param array 
	 * @return number
	 */
	public static function formula_add($data) {
    	if(!is_array($data)) {
    		return 0;
    	}
		$exids = $data['express'];
		$produce_id=$data['small_class_id'];
		unset($data['express']);
    	try{
    		$sql1 = "insert into `weixin_formula` set ";
    		$binds = array();
    		foreach ($data as $k => $v) {
    			$sql1 .= '`' . $k . '`=:' . $k . ',';
    			$binds[':' . $k] = $v;
    		}
    		$sql1 .= 'raw_add_time=NOW()';
			//添加属性
			$sql2 = "insert into `weixin_price_attr` set `produce_id`=:produce_id,`attr_id`=:attr_id,`raw_add_time`=NOW()";

			$pdo = self::DB()->dbh;
			$pdo->beginTransaction();

    		$stm1=$pdo -> prepare($sql1);
    		$stm1->execute($binds);

			$arrids = explode(',',$exids);
			$stm2 = $pdo -> prepare($sql2);
			foreach($arrids as $v){
    			$stm2->execute(array(':produce_id'=>$produce_id,':attr_id'=>$v));
			}
    		$pdo->commit();
			return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return false;
    	}
    }
    /*
     * 根据ID获取公式
     */
    public static function get_formula_by_id($id) {
    	$info = array();
    	if(empty($id)) {
    		return $info;
    	}
    	$sql = "select `formula_id`,`big_class_id`,`small_class_id`,`visible`,`delete_flag`,`raw_add_time`,`raw_update_time`,`editer`,`author`,`price_list` from `weixin_formula` where `formula_id`=:formula_id and `delete_flag` = 0";
		$binds[":class_name"] = $class_name;
    	try {
    		$st = self::DB() -> dbh -> prepare($sql);
    		$st -> execute(array(':formula_id' => $id));
    		$info = $st -> fetch();
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
    	return $info;
    }
    /**
     * @desc 更新分类
     * @param unknown $id
     * @param unknown $data
     * @return unknown|number
     */
    public static function update_formula_info($id, $data) {
    	try{
			$exids = $data['express'];
			$produce_id=$data['produce_id'];
			unset($data['express'],$data['produce_id']);
    		$sql1 = 'UPDATE `weixin_formula` SET ';
    		$binds = array();
    		foreach ($data as $k => $v) {
    			$sql1 .= '`' . $k . '`=:' . $k . ',';
    			$binds[':' . $k] = $v;
    		}
    		$ids = implode(',',$id);
    		$sql1 = trim($sql1,',');
    		$sql1 .= ' WHERE `formula_id` in ('.$ids.')';
			//删除全部属性
			$sql2 = "update `weixin_price_attr` set `delete_flag` = 1 where `produce_id`=:produce_id";
			//还原尚未删除的属性
			$sql3 = "update `weixin_price_attr` set `delete_flag` = 0 where `attr_id` = :attr_id and `produce_id`=:produce_id";
			//添加属性
			$sql4 = "insert into `weixin_price_attr` set `produce_id`=:produce_id,`attr_id`=:attr_id,`raw_add_time`=NOW()";
			$pdo = self::DB()->dbh;
			$pdo->beginTransaction();

    		$stm1 = $pdo -> prepare($sql1);
    		$stm1->execute($binds);

			$stm2 = $pdo -> prepare($sql2);
    		$stm2->execute(array(':produce_id'=>$produce_id));

			$arrids = explode(',',$exids);
			$stm3 = $pdo -> prepare($sql3);
			foreach($arrids as $v){
    			$stm3->execute(array(':produce_id'=>$produce_id, ':attr_id'=>$v));
				$rowCount = $stm3->rowCount();
				if(empty($rowCount)){
					$stm4 = $pdo -> prepare($sql4);
					$stm4->execute(array(':produce_id'=>$produce_id,':attr_id'=>$v));
				}
			}
    		$pdo->commit();
    		return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return false;
    	}
    }
	/*
	* @desc 获取小分类列表
	* @param 
	* @return array()
	**/
	public static function get_small_class($parent_id){
		$return = array();
		if(empty($parent_id)){
			return $return;
		}
		$sql = "select `product_id`,`produce_name`,`parent_id` from `weixin_product` where `parent_id` = :parent_id and `delete_flag` = 0 and `status` = 0";
		$binds = array(':parent_id' => $parent_id);
		try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds);
    		$return = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $return;
	}
	//获取公式表格类信息
	public static function get_class_info($id){
		$return = array();
		if(empty($id)){
			return $return;
		}
		$sql1 = "select `price_list`,`small_class_id` from `weixin_formula` where `formula_id`=:formula_id and `delete_flag` = 0";
		$sql2 = "select `produce_name` from `weixin_product` where `product_id`=:product_id and `delete_flag` = 0";
		//$sql3 = "select `marerial` from `weixin_basic_price` where `produce_id` = :produce_id and `delete_flag` = 0";
		$sql4 = "select `product_price_id` as id,`parent_id` as parentid,`price_name`,`depth` from `weixin_product_price`  where `delete_flag` = 0 ";
		//$sql5 = "select a.`area_id`,a.`province`,a.`city`,b.`transport_id` from `weixin_area` a left join `weixin_price_transport` b on a.`area_id`=b.`area_id` where b.`parent_id` = :parent_id and a.`delete_flag` = 0 and b.`delete_flag` = 0";
		try {
			$pdo = self::DB()->dbh;
    		$st = $pdo->prepare($sql1);
    		$st->execute(array(':formula_id'=>$id));
    		$formula = $st->fetch();
			if(!empty($formula['small_class_id'])){
				$st = $pdo->prepare($sql2);
				$st->execute(array(':product_id'=>$formula['small_class_id']));
				$return['class_info'] = $st->fetch();
				$return['class_info']['price_list'] = $formula['price_list'];

				/*$st = $pdo->prepare($sql3);
				$st->execute(array(':produce_id'=>$formula['small_class_id']));
				$return['basic_price'] = $st->fetchAll();

				$st = $pdo->prepare($sql5);
				$st->execute(array(':parent_id'=>$formula['small_class_id']));
				$return['area_price'] = $st->fetchAll();*/
			}
			if(!empty($formula['price_list'])){
				$list_arr = explode('/', $formula['price_list']);
				$list = array();
				foreach($list_arr as $v){
					$arr = explode(',', $v);
					$list = array_merge($list, $arr);
				}
				array_unique($list);
				$list = implode(',', $list);
				$st = $pdo->prepare($sql4." and `product_price_id` in ({$list})");
				$st->execute();
				$return['price_list'] = $st->fetchAll();
			}
			return $return;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $return;
	}

	//获取材质基础列表
	public static function get_marerial_group($id){
		$return = array();
		if(empty($id)){
			return $return;
		}
		$sql1 = "select `price_list`,`small_class_id` from `weixin_formula` where `formula_id`=:formula_id and `delete_flag` = 0";
		$sql3 = "select `basic_price_id`,`marerials`,`format`,`marerial` from `weixin_basic_price` where `produce_id` = :produce_id and `delete_flag` = 0";
		try {
			$pdo = self::DB()->dbh;
    		$st = $pdo->prepare($sql1);
    		$st->execute(array(':formula_id'=>$id));
    		$formula = $st->fetch();

			$st = $pdo->prepare($sql3);
			$st->execute(array(':produce_id'=>$formula['small_class_id']));
			$return = $st->fetchAll();
			return $return;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $return;
	}

	//获取分组地区列表
	public static function get_area_group($id){
		$return = array();
		if(empty($id)){
			return $return;
		}
		$sql1 = "select `price_list`,`small_class_id` from `weixin_formula` where `formula_id`=:formula_id and `delete_flag` = 0";
		$sql5 = "select a.`area_id`,a.`province`,a.`city`,a.`area`,a.`street`,b.`transport_id` from `weixin_area` a left join `weixin_price_transport` b on a.`area_id`=b.`area_id` where b.`parent_id` = :parent_id and a.`delete_flag` = 0 and b.`delete_flag` = 0 group by a.`city`";
		try {
			$pdo = self::DB()->dbh;
    		$st = $pdo->prepare($sql1);
    		$st->execute(array(':formula_id'=>$id));
    		$formula = $st->fetch();
			if(!empty($formula['small_class_id'])){
				$st = $pdo->prepare($sql5);
				$st->execute(array(':parent_id'=>$formula['small_class_id']));
				$return = $st->fetchAll();
			}
			return $return;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $return;
	}

	//批量修改数据
	public static function modify_batch($id, $data){
		if(empty($data)){
			return false;
		}
		$sql1 = "select `area_id` from `weixin_area` where `city` in (select `city` from `weixin_area` where `area_id` = :area_id and `delete_flag` = 0);";
		$sql2 = "select `marerial` from `weixin_basic_price` where `basic_price_id` = :basic_price_id and `delete_flag` = 0";
		$sql3 = "select `formula_table_id`,`attr_value` from `weixin_fomula_table` where `formula_id` = :formula_id and `attr_id` in ";
		$sql4 = "update `weixin_fomula_table` set `attr_value` = :attr_value where `formula_table_id` = :formula_table_id and `delete_flag` = 0";
		try {
			$pdo = self::DB()->dbh;
			foreach($data['marerials'] as $key => $value){
				//地区查询
				$st = $pdo->prepare($sql1);
				$st->execute(array(":area_id"=>$data['area'][$key]));
				$areas = $st->fetchAll();
				if(empty($areas)){
					throw new Exception("地区查询为空");
				}
				foreach($areas as $v){
					for($i=1;$i<6;$i++)
						$area[] = "trans{$i}{$v['area_id']}";
				}
				//材质查询
				$st = $pdo->prepare($sql2);
				$st->execute(array(":basic_price_id"=>$data['marerials'][$key]));
				$marerials = $st->fetchAll();
				if(empty($marerials)){
					throw new Exception("材质查询为空");
				}
				foreach($marerials as $v){
					$marerial = json_decode($v['marerial'], true);
				}
				//价格查询
				$trans = implode("','", $area);
				$st = $pdo->prepare("{$sql3} ('{$trans}')");
				$st->execute(array(":formula_id" => $id));
				$price = $st->fetchAll();
				if(empty($price)){
					throw new Exception("价格查询为空");
				}
				//数据修该
				foreach($price as $v){
					$table_id = $v['formula_table_id'];
					$table_data = json_decode($v['attr_value'], true);
					if(!empty($table_data)){
						foreach($table_data as $k=>$v){
							if(in_array($k, $marerial)){
								$v_ = floatval($table_data[$k]);
								if($data['action'][$key] == 1){
									$table_data[$k] = $v_ + $data['range'][$key];
								}else{
									$v_ = $v_ - $data['range'][$key];
									$table_data[$k] = $v_ >=0?$v_:0;
								}
							}
						}//var_dump($table_data);exit;
						$st = $pdo->prepare($sql4);
						$st->execute(array(":attr_value" => json_encode($table_data), ":formula_table_id" => $table_id));
					}
				}
			}
			return true;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return false;
	}

	//保存表格数据
	public static function insert_formula_table_data($data, $formula_id, $transport = array()){
		if(empty($data) || empty($formula_id)){
			return false;
		}
		$sql = "select `small_class_id` from `weixin_formula` where `formula_id`=:formula_id";
		//$sql1 = "update `weixin_fomula_table` set `delete_flag` = 1 where `formula_id` = :formula_id";
		//$sql2 = "update `weixin_fomula_table` set `attr_value` = :attr_value, `delete_flag` = 0 where `formula_id` = :formula_id and `attr_id` = :attr_id and `type`=:type";
		//$sql3 = "insert into `weixin_fomula_table` (`formula_id`,`attr_id`,`attr_value`,`type`,`raw_add_time`) values(:formula_id,:attr_id,:attr_value,:type,NOW())";
		$sql4 = "update `weixin_price_attr` set `pre_price`=`cur_price`,`cur_price`=:cur_price1 where `produce_id`=:produce_id and `attr_id`=:attr_id and `cur_price`!=:cur_price2";
		try {
			$pdo = self::DB()->dbh;
			$pdo->beginTransaction();
			$st = $pdo->prepare($sql);
    		//$st1 = $pdo->prepare($sql1);
			//$st2 = $pdo->prepare($sql2);
			//$st3 = $pdo->prepare($sql3);
			$st4 = $pdo->prepare($sql4);
			//$st1->execute(array(':formula_id' => $formula_id));
			$st->execute(array(':formula_id' => $formula_id));
			$formula = $st->fetch();
			$produce_id = $formula['small_class_id'];
			foreach($data as $v){
				/*$v['type'] = 0;
				$st2->execute($v);
				$rowCount = $st2->rowCount();
				if(empty($rowCount)){
					$st3->execute($v);
				}*/
				$price = json_decode($v[':attr_value'], true);
				$st4->execute(array(':cur_price1'=>$price['range'][1],':produce_id'=>$produce_id,':attr_id'=>$v[':attr_id'],':cur_price2'=>$price['range'][1]));
			}
			/*if(!empty($transport)){
				foreach($transport as $v){
					$v['type'] = 1;
					$st2->execute($v);
					$rowCount = $st2->rowCount();
					if(empty($rowCount)){
						$st3->execute($v);
					}
				}
			}*/
    		$pdo->commit();
    		return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return false;
	}
	//保存表格数据
	public static function insert_general_table_data($data){
		if(empty($data)){
			return false;
		}
		try {
			$pdo = self::DB()->dbh;
			$pdo->beginTransaction();
			foreach($data as $v){
				$produce_id = implode(',', $v[':produce_id']);
				$sql = "update `weixin_price_attr` set `pre_price`=`cur_price`,`cur_price`=:cur_price1 where `produce_id` in ({$produce_id}) and `attr_id`=:attr_id and `cur_price`!=:cur_price2";
				$st = $pdo->prepare($sql);
				$price = json_decode($v[':attr_value'], true);
				$st->execute(array(':cur_price1'=>$price['range'][1],':attr_id'=>$v[':attr_id'],':cur_price2'=>$price['range'][1]));
			}
    		$pdo->commit();
    		return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return false;
	}

	//获取表格数据
	public static function get_formula_table_data($produce_id){
		$return = array();
		if(empty($produce_id)){
			return $return;
		}
		//$sql = "select `formula_id`,`attr_id`,`attr_value`,`raw_add_time` from `weixin_fomula_table` where `formula_id` = :formula_id and `delete_flag` = 0 and `type`=:type";
		try {
			$sql1 = "select `attr_id` from `weixin_price_attr` where `produce_id`=:produce_id and `delete_flag`=0";
			$pdo = self::DB()->dbh;
    		$st1 = $pdo->prepare($sql1);
			$st1->execute(array(':produce_id' => $produce_id));
			$attr_id = $st1->fetchAll();
			if(empty($attr_id)){
				return $return;
			}
			foreach($attr_id as $v){
				$ids[] = $v['attr_id'];
			}
			$ids = implode(',', $ids);
			$sql2 = "select `price_attr_id`,`pre_price`,`cur_price`,`attr_id` from `weixin_price_attr` where `produce_id`=:produce_id and `attr_id` in ({$ids}) and `delete_flag`=0";
			$st2 = $pdo->prepare($sql2);
			$st2->execute(array(':produce_id' => $produce_id));
			$result = $st2->fetchAll();
			if(empty($result)){
				return $return;
			}
			foreach($result as $key=>$value){
				$return['price_data'][$key]['attr_id'] = $value['attr_id'];
				$return['price_data'][$key][] = array('range'=>array($value['cur_price'],$value['cur_price'],$value['cur_price']-$value['pre_price'],$value['price_attr_id']));
			}
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
		return $return;
	}
}
